﻿/* =========================================================================== 
| Autors: Arnau Solsona, Javier Alonso, Jordi Mateu, Maria Antonia, Xavi Font
| Projecte: eOlympics
| Mòdul: scriptCreacioBD.sql
| Descripció: Script de creació de la base de dades PAC4
| 
| Revisions:
| Data		 Nom	         Revisió
| ---------- --------------- ---------------------------------------
| 07-05-2012 Xavi Font		 Creat
| 20-05-2012 Xavi Font		 Última modificació
| 
 ============================================================================== */

--Creacio de la base de dades
-- (cal executar aquestes dues comandes una a una des del SQL Editor o executar-les per commanda)
--DROP DATABASE IF EXISTS "PAC4";
--CREATE DATABASE "PAC4" WITH ENCODING='UTF8' OWNER=postgres CONNECTION LIMIT=-1;

DROP SCHEMA IF EXISTS public CASCADE;
CREATE SCHEMA public;

-- Creació de taules
BEGIN WORK;
SET TRANSACTION READ WRITE;

/*
##############	Creació de la TAULA GENERAL de l'aplicació eOlympics  ##############

	Hi ha diverses opcions de parametrització de variables globals a la BBDD.

	1. La més comú és configurar les variables globals que necessita la BBDD per ser
	parametritzable al arxiu de configuració de PostgreSQL "postgresql.conf",
	aquest fitxer es trova al subdirectori "data" del directori del servidor PostgreSQL.
	Al final d'aquest fitxer hi hem d'afegir les següent linies per a poder fer ús de les
	variables globals.

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

custom_variable_classes = 'eOlympics'		# list of custom variable class names
eOlympics.min_participacio = 3	# mínim participants a una ronda
eOlympics.max_participacio = 15	# mínim participants a una ronda

	Per a més informació:
		http://www.postgresql.org/docs/8.4/static/runtime-config-custom.html
		http://www.postgresql.org/docs/8.4/static/functions-admin.html

	2. Per estalviar-nos modificar el fitxer de configuració del servidor i posteriorment
	executar pg_reload_conf() creem una taula que emmagatzemarà les variables globals així
	com creem també les funcions necessàries per a consultar-la i modificar-la amb facilitat.

*/

-- Crea la taula general que emmagatzemarà les variables globals
CREATE TABLE taula_general(name text PRIMARY KEY, value text, description text);

-- Funció per afegir o modificar una variable global amb commentaris
CREATE FUNCTION put_var(key text, data text, commentaries text) RETURNS void AS $$
BEGIN
  LOOP
    UPDATE taula_general SET value = data, description = commentaries WHERE name = key;
    IF found THEN
      RETURN;
    END IF;
    BEGIN
      INSERT INTO taula_general(name, value, description) VALUES (key, data, commentaries);
      RETURN;
    EXCEPTION WHEN unique_violation THEN
    	-- torna a intentar modificar la variable global
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Funció per afegir o modificar una variable global
CREATE FUNCTION put_var(key text, data text) RETURNS void AS $$
BEGIN
  LOOP
    UPDATE taula_general SET value = data WHERE name = key;
    IF found THEN
      RETURN;
    END IF;
    BEGIN
      INSERT INTO taula_general(name, value) VALUES (key, data);
      RETURN;
    EXCEPTION WHEN unique_violation THEN
    	-- torna a intentar modificar la variable global
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Funció que obtindrà el valor de texte d'una variable global
CREATE FUNCTION get_var(text) RETURNS text AS $$
  SELECT value FROM taula_general where name = $1
$$ LANGUAGE sql;

-- Funció que obtindrà el valor enter d'una variable global
CREATE FUNCTION get_var_int(text) RETURNS integer AS $$
	SELECT CAST(to_number(value, 'S0000000000') AS integer)
		FROM taula_general where name = $1
$$ LANGUAGE sql;

-- Funció que obtindrà el valor decimal d'una variable global
CREATE FUNCTION get_var_double(text) RETURNS double precision AS $$
	SELECT CAST(to_number(value, 'S0000000000.0000000000') AS double precision)
		FROM taula_general where name = $1
$$ LANGUAGE sql;

-- Funció que esborrarà una variable global
CREATE FUNCTION del_var(text) RETURNS void AS $$
  DELETE FROM taula_general WHERE name = $1
$$ LANGUAGE sql;

/* ################ Definició de les variables globals de la BBDD ################ */

SELECT put_var('prova.estat_fora_periode', 'ForaPeriode',
	'Estat prova amb la inscripció fora de període');
SELECT put_var('prova.estat_inscripcio', 'Inscripcio',
	'Estat prova que admet inscripcions (dins període i no assolida capacitat)');
SELECT put_var('prova.estat_ple', 'Ple',
	'Estat prova amb el màxim d''inscripcions realitzades');
SELECT put_var('prova.estat_deserta', 'Deserta',
	'Estat prova amb inscripcions insuficients i ha acabat llur període d''inscripció');
SELECT put_var('prova.estat_inscripcio_tancada', 'InscripcioTancada',
	'Estat prova en que els estudiants esperen competir a la eliminatòria');
SELECT put_var('prova.estat_eliminatoria_competicio', 'EliminatoriaCompeti',
	'Estat prova en que els estudiants poden competir a la eliminatòria');
SELECT put_var('prova.estat_eliminatoria_tancada', 'EliminatoriaTancada',
	'Estat prova després de tancar competició de la ronda eliminatòria');
SELECT put_var('prova.estat_final_competicio', 'FinalCompeticio',
	'Estat prova en que els estudiants poden competir en la final');
SELECT put_var('prova.estat_final_tancada', 'FinalTancada',
	'Estat prova després de tancar competició de la ronda final');

SELECT put_var('prova.min_participants', '3',
	'Mínim nombre de participants a una prova.');
SELECT put_var('prova.max_participants', '25',
	'Màxim nombre de participants a una prova.');
SELECT put_var('prova.max_participants_final', '10',
	'Màxim nombre de participants a una final.');
SELECT put_var('ronda.min_preguntes', '6',
	'Mínim nombre de preguntes en una ronda.');
SELECT put_var('ronda.max_preguntes', '15',
	'Màxim nombre de preguntes en una ronda.');
SELECT put_var('classificacio.puntuacio_encerts', '1.0',
	'Puntuació per a cada pregunta encertada.');
SELECT put_var('classificacio.puntuacio_errors', '-0.5',
	'Puntuació per a cada pregunta equivocada.');
SELECT put_var('classificacio.puntuacio_enblanc', '0.0',
	'Puntuació per a cada pregunta en blanc.');

/* ################ Fi definició variables globals de la BBDD ################ */

CREATE TABLE USUARI (
    idIntern integer NOT NULL,	
    tipusUsuari character(1) NOT NULL, -- A Administrador, R Responsable d'events i E Estudiant 
    idUsuari character varying(16) UNIQUE NOT NULL,
    pwd character(16) NOT NULL,
    email character varying(64) UNIQUE NOT NULL,
    estatEnSistema character(1) NOT NULL,  -- A Actiu i B Bloquejat
    tipusDocumentId character(3) NOT NULL, -- NIF, NIE o ALT
    document character varying(16) NOT NULL,
    nom character varying(32) NOT NULL,
    cognom1 character varying(32) NOT NULL,
    cognom2 character varying(32),
    dataAlta date,
    usuariAlta integer,
    dataBaixa date,
    usuariBaixa integer,
    dataMod date,
    usuariMod integer,
    CONSTRAINT pk_Usuari PRIMARY KEY (idIntern),
    CONSTRAINT fk_Usuari_Alta_Usuari FOREIGN KEY (usuariAlta) REFERENCES USUARI (idIntern),
    CONSTRAINT fk_Usuari_Baixa_Usuari FOREIGN KEY (usuariBaixa) REFERENCES USUARI (idIntern),
    CONSTRAINT fk_Usuari_Mod_Usuari FOREIGN KEY (usuariMod) REFERENCES USUARI (idIntern)   
);

CREATE SEQUENCE USUARI_idIntern_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE USUARI_idIntern_seq OWNED BY USUARI.idIntern;
SELECT pg_catalog.setval('USUARI_idIntern_seq', 1, false);
ALTER TABLE USUARI ALTER COLUMN idIntern SET DEFAULT nextval('USUARI_idIntern_seq'::regclass);

CREATE TABLE UNIVERSITAT (
    idUniversitat integer NOT NULL,
    nomUniversitat character varying(100) NOT NULL,
    CONSTRAINT pk_Universitat PRIMARY KEY (idUniversitat),
    CONSTRAINT uk_Universitat UNIQUE (nomUniversitat)
);

CREATE SEQUENCE UNIVERSITAT_idUniversitat_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE UNIVERSITAT_idUniversitat_seq OWNED BY UNIVERSITAT.idUniversitat;
SELECT pg_catalog.setval('UNIVERSITAT_idUniversitat_seq', 1, false);
ALTER TABLE UNIVERSITAT ALTER COLUMN idUniversitat SET DEFAULT nextval('UNIVERSITAT_idUniversitat_seq'::regclass);

CREATE TABLE ESTUDIANT (
    idEstudiant integer NOT NULL,
    idUniversitat integer NOT NULL,
    CONSTRAINT pk_Estudiant PRIMARY KEY (idEstudiant),	
    CONSTRAINT fk_Usuari_Est FOREIGN KEY (idEstudiant) REFERENCES USUARI (idIntern),
    CONSTRAINT fk_Universitat_Est FOREIGN KEY (idUniversitat) REFERENCES UNIVERSITAT (idUniversitat)
);

CREATE TABLE RESPONSABLEEVENTS (
    idResponsable integer NOT NULL,
    CONSTRAINT pk_ResponsableEvents PRIMARY KEY (idResponsable),	
    CONSTRAINT fk_Usuari_RespEv FOREIGN KEY (idResponsable) REFERENCES USUARI (idIntern)
);

CREATE TABLE ADMINISTRADOR (
    idAdministrador integer NOT NULL,
    CONSTRAINT pk_Administrador PRIMARY KEY (idAdministrador),	
    CONSTRAINT fk_Usuari_Admin FOREIGN KEY (idAdministrador) REFERENCES USUARI (idIntern)
);

CREATE TABLE PROVA (
    idProva integer NOT NULL,
    descripcio character varying(50) NOT NULL,
    dataIniInscripcio date NOT NULL,
    dataFiInscripcio date NOT NULL,
    nombrePlaces integer NOT NULL,
    estat character varying(20),
    dataAlta date,
    usuariAlta integer,
    dataBaixa date,
    usuariBaixa integer,
    dataMod date,
    usuariMod integer,
    CONSTRAINT pk_Prova PRIMARY KEY (idProva),
    CONSTRAINT ck_DataInsc CHECK (dataIniInscripcio <= dataFiInscripcio),  	
    CONSTRAINT fk_Usuari_Alta_Prova FOREIGN KEY (usuariAlta) REFERENCES RESPONSABLEEVENTS (idResponsable),
    CONSTRAINT fk_Usuari_Baixa_Prova FOREIGN KEY (usuariBaixa) REFERENCES RESPONSABLEEVENTS (idResponsable),
    CONSTRAINT fk_Usuari_Mod_Prova FOREIGN KEY (usuariMod) REFERENCES RESPONSABLEEVENTS (idResponsable)	
);

CREATE SEQUENCE PROVA_idProva_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE PROVA_idProva_seq OWNED BY PROVA.idProva;
SELECT pg_catalog.setval('PROVA_idProva_seq', 1, false);
ALTER TABLE PROVA ALTER COLUMN idProva SET DEFAULT nextval('PROVA_idprova_seq'::regclass);

CREATE TABLE RONDA (
    idProva integer NOT NULL,
    tipusRonda character(1) NOT NULL,  -- F si és final i E si és eliminatoria
    nombrePreguntes integer NOT NULL,
    dataIniciRealitzacio date NOT NULL,
    dataFiRealitzacio date NOT NULL,
    dataAlta date,
    usuariAlta integer,
    dataBaixa date,
    usuariBaixa integer,
    dataMod date,
    usuariMod integer,
    CONSTRAINT pk_Ronda PRIMARY KEY (idProva, tipusRonda),
    CONSTRAINT ck_DataInsc CHECK (dataIniciRealitzacio <= dataFiRealitzacio),  	
    CONSTRAINT fk_Prova_Rond FOREIGN KEY (idProva) REFERENCES PROVA (idProva),  
    CONSTRAINT fk_Usuari_Alta_Ronda FOREIGN KEY (usuariAlta) REFERENCES RESPONSABLEEVENTS (idResponsable),
    CONSTRAINT fk_Usuari_Baixa_Ronda FOREIGN KEY (usuariBaixa) REFERENCES RESPONSABLEEVENTS (idResponsable),
    CONSTRAINT fk_Usuari_Mod_Ronda FOREIGN KEY (usuariMod) REFERENCES RESPONSABLEEVENTS (idResponsable)
);

CREATE TABLE RESPOSTACORRECTA (
    idProva integer NOT NULL,
    tipusRonda character(1) NOT NULL,
    numPregunta integer NOT NULL,
    opcioCorrecta character(1) NOT NULL,
    dataAlta date,
    usuariAlta integer,
    dataBaixa date,
    usuariBaixa integer,
    dataMod date,
    usuariMod integer,
    CONSTRAINT pk_RespostaCorrecta PRIMARY KEY (idProva, tipusRonda, numPregunta),	
    CONSTRAINT fk_Ronda_RC FOREIGN KEY (idProva, tipusRonda) REFERENCES RONDA (idProva, tipusRonda),
    CONSTRAINT fk_Usuari_Alta_RC FOREIGN KEY (usuariAlta) REFERENCES RESPONSABLEEVENTS (idResponsable),
    CONSTRAINT fk_Usuari_Baixa_RC FOREIGN KEY (usuariBaixa) REFERENCES RESPONSABLEEVENTS (idResponsable),
    CONSTRAINT fk_Usuari_Mod_RC FOREIGN KEY (usuariMod) REFERENCES RESPONSABLEEVENTS (idResponsable)
);

CREATE TABLE INSCRIPCIO (
    idProva integer NOT NULL,
    idEstudiant integer NOT NULL,
    dataInscripcio date NOT NULL,
    CONSTRAINT pk_Inscripcio PRIMARY KEY (idProva, idEstudiant),	
    CONSTRAINT fk_Prova_Ins FOREIGN KEY (idProva) REFERENCES PROVA (idProva),
    CONSTRAINT fk_Estudiant_Ins FOREIGN KEY (idEstudiant) REFERENCES ESTUDIANT (idEstudiant)
);

CREATE TABLE PARTICIPACIO (
    idProva integer NOT NULL,
    tipusRonda character(1) NOT NULL,
    idEstudiant integer NOT NULL,
    dataParticipacio date NOT NULL,
    CONSTRAINT pk_Participacio PRIMARY KEY (idProva, tipusRonda, idEstudiant),	
    CONSTRAINT fk_Inscripcio_Part FOREIGN KEY (idProva, IdEstudiant) REFERENCES INSCRIPCIO (idProva, idEstudiant),
    CONSTRAINT fk_Ronda_Part FOREIGN KEY (idProva, tipusRonda) REFERENCES RONDA (idProva, tipusRonda)
);

CREATE TABLE RESPOSTAESTUDIANT (
    idProva integer NOT NULL,
    tipusRonda character(1) NOT NULL,
    idEstudiant integer NOT NULL,
    numPregunta integer NOT NULL,
    opcioMarcada character(1),
    CONSTRAINT pk_RespostaEstudiant PRIMARY KEY (idProva, tipusRonda, idEstudiant, numPregunta),	
    CONSTRAINT fk_Participacio_RE FOREIGN KEY (idProva, tipusRonda, idEstudiant) REFERENCES PARTICIPACIO (idProva, tipusRonda, idEstudiant)
);

CREATE TABLE CLASSIFICACIO (
    idProva integer NOT NULL,
    tipusRonda character(1) NOT NULL,
    idEstudiant integer NOT NULL,
    posicio integer NOT NULL,
    encerts integer NOT NULL,
    errors integer NOT NULL,
    enBlanc integer NOT NULL,
    puntuacio double precision NOT NULL,
    CONSTRAINT pk_Classificacio PRIMARY KEY (idProva, tipusRonda, idEstudiant),
    CONSTRAINT fk_Participacio_Class FOREIGN KEY (idProva, tipusRonda, idEstudiant) REFERENCES PARTICIPACIO (idProva, tipusRonda, idEstudiant)
);
COMMIT;

--=====================================================================================================================
